package com.hnkywl.sts.dao.organ;

import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.entity.sys.Yg;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * 说明：
 * 包:        com.hnkywl.sts.dao.sys
 * 类描述:    岗位dao类
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class GsjgDao extends SimpleHibernateTemplate<Gsjg, Long> {

    /**
     * 查询岗位列表
     *
     * @param mc       qc
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Gsjg> findGsjgList(Long id, String mc, Long parentId, String parentMc, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Gsjg> page = new Page<Gsjg>(pageNo, pageSize, 0);
        //==============条件拼接===============

        if (id != null) {
            condition.append(" id = ? ");
            param.add(id);
        } else {
            if (mc != null && !mc.isEmpty()) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" jc like ? ");
                param.add("%" + mc.trim() + "%");
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" qc like ? ");
                param.add("%" + mc.trim() + "%");
            }
        }

        if (parentId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sjjg.id = ? ");
            param.add(parentId);
        } else {
            if (parentMc != null && !parentMc.isEmpty()) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" sjjg.jc like ? ");
                param.add("%" + parentMc.trim() + "%");
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" sjjg.qc like ? ");
                param.add("%" + parentMc.trim() + "%");
            }
        }
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" (type = ? or type = ?) ");
        param.add("nbgs");
        param.add("nbbm");

        String dataRegion = "";
        if (YgService.getCurrentYgCxfw() == 2 || YgService.getCurrentYgCxfw() == 3) {
            dataRegion += " id=" + YgService.getCurrentYgGsId() + " and ";
        }

        //==============记录数量===============
        hql = "select count(*) from Gsjg";
        if (condition.length() != 0)
            hql = hql + " where " + dataRegion + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Gsjg ";
        if (condition.length() != 0)
            hql = hql + " where " + dataRegion + condition + "order by type asc, pxh asc,id desc";
        if (param.size() != 0)
            return this.find(page, hql, param.toArray());
        return find(page, hql);
    }

    /**
     * 查询计划单位
     *
     * @param qc
     * @param jc
     * @param lxr
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Gsjg> findJhdwList(String qc, String jc, String lxr, int pageNo, int pageSize) {
        String hql = null;
        String lxrHql = "";
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Gsjg> page = new Page<Gsjg>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (qc != null && !qc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" qc like ? ");
            param.add("%" + qc.trim() + "%");
        }
        if (jc != null && !jc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append("( jc like ? or qc like ?)");
            param.add("%" + jc.trim() + "%");
            param.add("%" + jc.trim() + "%");
        }
        if (lxr != null && !lxr.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" lxr like ? ");
            param.add("%" + lxr.trim() + "%");
        }

        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" (lskh = false or lskh is null) ");
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" type in ('qyjg','nbgs') ");
        //==============记录数量===============
        hql = "select count(*) from Gsjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Gsjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition + "order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    /**
     * 查询所有根节点公司机构
     *
     * @return
     */
    public List<Gsjg> findAllRootGsjg() {
        return find("from Gsjg where id=? and (type = ? or type = ?) order by pxh", YgService.getCurrentYgZgGsId(), "nbgs", "nbbm");
    }

    /**
     * 根据全称找企业机构 和 内部公司
     *
     * @return
     */
    public Gsjg findGsjgFromQc(String qc) {
        List<Gsjg> gsjgList = find("from Gsjg where qc = ? ", qc);
        return CollectionUtils.isEmpty(gsjgList) ? null : gsjgList.get(0);
    }

    //根据code查询
    public Gsjg findGsjgByCode(String code) {
        return findUniqueByProperty("bm", code);
    }

    //所属机构
    public List<Gsjg> findSsgsjgList() {
        String sql = "select * from T_SYS_YG_GSJG s where s.yg_id = :ygId";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setLong("ygId", YgService.getCurrentYg().getId());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map> map = sqlQuery.list();
        List<Gsjg> gsjgs = new ArrayList<Gsjg>();
        for (int i = 0; i < map.size(); i++) {
            Gsjg g = this.get(((BigDecimal) map.get(i).get("GSJG_ID")).longValue());
            gsjgs.add(g);
        }
        return gsjgs;
    }

    /**
     * 查找当前登录员工的上级机构的ID
     *
     * @param id
     * @return
     */
    public Object findSjjgName(Long id) {
        String sql = "select g.sjjg_id from t_sys_gsjg g left join t_sys_yg yg on g.id = yg.zgjg_id where yg.id = :ygid";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setLong("ygid", id);
        Object value = sqlQuery.uniqueResult();
        return value;
    }

    public List<Map> getTree(String ids, String cxfw) {
        String sql = StringUtils.EMPTY;
        if (cxfw.equals("2") || cxfw.equals("3")) {
            sql = "select t.*, (select count(" + YgService.getCurrentYgGsId() + ") from t_sys_gsjg g1 where g1.sjjg_id = t.id) as gsjgList from " +
                    "(select sfzx as sfzx ,id as id, jc as text, sjjg_id as pid, pxh as pxh, type as type" +
                    "  from t_sys_gsjg" +
                    " where type in ('nbgs', 'nbbm')" +
                    " and deleted = 0 and sfzx = 0 start with id in (" + YgService.getCurrentYgGsId() + ") connect by prior sjjg_id = id  order by type asc,pxh asc,id desc) t";
        } else {
            sql = "select t.*, (select count(" + YgService.getCurrentYgZgGsId() + ") from t_sys_gsjg g1 where g1.sjjg_id = t.id) as gsjgList from " +
                    "(select sfzx as sfzx ,id as id, jc as text, sjjg_id as pid, pxh as pxh, type as type" +
                    "  from t_sys_gsjg" +
                    " where type in ('nbgs', 'nbbm')" +
                    " and deleted = 0 and sfzx = 0 start with id in (" + YgService.getCurrentYgZgGsId() + ") connect by prior id = sjjg_id  order by type asc,pxh asc,id desc) t";
        }
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    //公司机构树包括未启用的
    public List<Map> getTreeZx(String ids, Integer cxfw) {
        String sql = StringUtils.EMPTY;
        if (cxfw == 2 || cxfw == 3) {
            sql = "select t.*, (select count(" + YgService.getCurrentYgGsId() + ") from t_sys_gsjg g1 where g1.sjjg_id = t.id) as gsjgList from " +
                    "(select sfzx as sfzx ,id as id, jc as text, sjjg_id as pid, pxh as pxh, type as type" +
                    "  from t_sys_gsjg" +
                    " where type in ('nbgs', 'nbbm')" +
                    " and deleted = 0 start with id in (" + YgService.getCurrentYgGsId() + ") connect by prior sjjg_id = id  order by type asc,pxh asc,id desc) t";
        } else {
            sql = "select t.*, (select count(" + YgService.getCurrentYgZgGsId() + ") from t_sys_gsjg g1 where g1.sjjg_id = t.id) as gsjgList from " +
                    "(select sfzx as sfzx ,id as id, jc as text, sjjg_id as pid, pxh as pxh, type as type" +
                    "  from t_sys_gsjg" +
                    " where type in ('nbgs', 'nbbm')" +
                    " and deleted = 0 start with id in (" + YgService.getCurrentYgZgGsId() + ") connect by prior id = sjjg_id  order by type asc,pxh asc,id desc) t";
        }
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    public List<Map> getTreeJy(String ids) {
        String sql = "select id as id,jc as text,sjjg_id as pid,type as type from t_sys_gsjg where type in ('nbgs','nbbm') " +
                "and deleted = 0 and sfzx = 0 and (type = 'nbgs' or (type='nbbm' and sfjyzz =1)) start with id in (" + ids + ") connect by prior id = sjjg_id order by id desc";
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    // 无权限公司机构树
    public List<Map> getTreeJywqx(String ids) {
        String sql = "select id as id,jc as text,sjjg_id as pid,type as type from t_sys_gsjg where type in ('nbgs','nbbm') " +
                "and deleted = 0 and sfzx = 0 and (type = 'nbgs' or (type='nbbm' and sfjyzz =1)) start with id in (1) connect by prior id = sjjg_id order by id desc";
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    public List<Map> getSsTree(String ids) {
        String sql = "select id as id,jc as text,sjjg_id as pid,type as type from t_sys_gsjg where type in ('nbgs','nbbm') " +
                "and deleted = 0 and sfzx = 0 start with id in (" + ids + ") connect by prior id = sjjg_id order by id desc";
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    public List<Map> getGsbb(String ids) {
        String sql = "select id as id, jc as text, sjjg_id as pid, type as type from t_sys_gsjg where deleted = 0 and sfzx = 0" +
                " start with id in (" + ids + ") connect by prior id = sjjg_id and type = 'nbbm' and sfjyzz = 1 order by pxh asc";
        SQLQuery slqQuery = getSession().createSQLQuery(sql);
        slqQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return slqQuery.list();
    }

    /**
     * 获取同层次的当前最大的排序号
     *
     * @param gsjgId 公司机构的Id
     * @return
     */
    public Integer findMaxPxh(Long gsjgId) {
        String hql = "select MAX(pxh) from Gsjg where sjjg.id = ? and (type = ? or type = ?)";
        return findInt(hql, gsjgId, "nbbm", "nbgs");
    }

    /**
     * 获取所有的内部机构
     *
     * @param id
     * @return
     */
    public List<Gsjg> findNbjgById(Long id) {
        return find("from Gsjg where sjjg.id = ?", id);
    }

    public Gsjg findKhByEsbcode(String code) {
        String hql = "from Gsjg where bm = ?  ";
        return find(hql, code).size() <= 0 ? null : (Gsjg) find(hql, code).get(0);
    }

    /**
     * 根据名称获取公司员工
     *
     * @param name
     * @return
     */
    public List findGsjgYgDetailByName(String name) {
        Criteria criteria = this.getSession().createCriteria(Yg.class)
                .createAlias("zgjg", "zgjg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xm").as("xm"))
                ).add(Property.forName("zgjg.jc").eq(name)).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }

    /**
     * 根据ID获取公司员工
     *
     * @param id
     * @return
     */
    public List findGsjgYgDetailById(Long id) {
        Criteria criteria = this.getSession().createCriteria(Yg.class)
                .createAlias("zgjg", "zgjg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xm").as("xm"))
                ).add(Property.forName("zgjg.id").eq(id)).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }

    /**
     * 判断是否选中内部机构
     *
     * @param id
     * @param propertyValue
     * @param propertyName
     * @return
     */
    public Boolean checkUnique(Long id, Object propertyValue, String propertyName, Long sjjgId) {
        String hql;
        if (propertyValue == null)
            return false;
        if (id == null) {
            //新增检验
            if (propertyName.equals("jc") && sjjgId != null) {
                hql = "from Gsjg where " + propertyName + " = ? and sjjg.id = ?";
                return this.existsQuery(hql, propertyValue, sjjgId);
            }
            if (propertyName.equals("qc") && sjjgId != null) {
                hql = "from Gsjg where " + propertyName + " = ? and sjjg.id = ? ";
                return this.existsQuery(hql, propertyValue, sjjgId);
            } else {
                hql = "from Gsjg where " + propertyName + " = ? ";
                return this.existsQuery(hql, propertyValue);
            }
        } else {
            //修改检验
            if (propertyName.equals("jc") && sjjgId != null) {
                hql = "from Gsjg where " + propertyName + " = ? and sjjg.id = ? and id <>?";
                return this.existsQuery(hql, propertyValue, sjjgId, id);
            }
            if (propertyName.equals("qc") && sjjgId != null) {
                hql = "from Gsjg where " + propertyName + " = ? and sjjg.id = ? and id <>?";
                return this.existsQuery(hql, propertyValue, sjjgId, id);
            } else {
                hql = "from Gsjg where " + propertyName + " = ? and id <> ?";
                return this.existsQuery(hql, propertyValue, id);
            }
        }
    }

    /**
     * 判断是否选中内部机构
     *
     * @param id
     * @param propertyValue
     * @param propertyName
     * @return
     */
    public Boolean checkUniqueJc(Long id, Object propertyValue, String propertyName) {
        String hql;
        if (propertyValue == null)
            return false;
        if (id == null) {
            //修改检验
            hql = "from Gsjg where " + propertyName + " = ? ";
            return this.existsQuery(hql, propertyValue);
        } else {
            //新增检验
            hql = "from Gsjg where " + propertyName + " = ? and id <> ?";
            return this.existsQuery(hql, propertyValue, id);
        }
    }

    /**
     * 查询所有的企业机构和内部公司
     *
     * @param jc
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Map> findGsjgAndQsjgJcList(String jc, int pageNo, int pageSize) {
        String select = "select  g.id  as \"id\",  g.jc  as  \"jc\" ";
        String from = " from t_sys_gsjg g where g.type in ('nbgs','qyjg') and g.deleted = 0 ";
        StringBuilder where = new StringBuilder();
        if (jc != null) {
            where.append(" and ");
            where.append("g.jc like :jc");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where);
        if (jc != null) {
            query.setString("jc", "%" + jc.trim() + "%");
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }


    public List<Map> findGsjgAndQsjgQcList(String qc, int pageNo, int pageSize) {
        String select = "select  g.id  as \"id\",  g.qc  as  \"qc\" ";
        String from = " from t_sys_gsjg g where g.type in ('nbgs','qyjg')  and g.deleted = 0   ";
        String order = " order by length(g.qc) ";
        StringBuilder where = new StringBuilder();
        if (qc != null) {
            where.append(" and ");
            where.append(" (g.qc like :qc or g.jc like :qc) ");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where + order);
        if (qc != null) {
            query.setString("qc", "%" + qc.trim() + "%");
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public List<Map> findXyKhByHtId_qclist(Long htId, String qc, int pageNo, int pageSize) {
        String select = "select  distinct kh.id  as \"id\",  kh.qc  as  \"qc\" ";
        String from = "from  t_biz_ht ht,t_sys_gsjg kh   " +
                " where kh.deleted = 0  and ht.deleted = 0 and ht.hzf_id = kh.id and ht.zddhcght_id is not null  ";
        String order = " order by length(kh.qc) ";
        StringBuilder where = new StringBuilder();
        if (qc != null) {
            where.append(" and ");
            where.append(" (kh.qc like :qc or kh.jc like :qc) ");
        }
        if (htId != null) {
            where.append(" and ");
            where.append(" ht.zddhcght_id = :htId  ");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where + order);
        if (qc != null) {
            query.setString("qc", "%" + qc.trim() + "%");
        }
        if (htId != null) {
            query.setLong("htId", htId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }


    public List<Map> findXyKhByHtId_qclist1(Long htId, String qc, int pageNo, int pageSize) {
        String select = " select   distinct g.id  as \"id\",  g.qc  as  \"qc\" ";
        String from = " from t_biz_ht ht \n" +
                "                      left join t_sys_gsjg  g on g.id=ht.hzf_id\n" +
                "                        where g.deleted = 0  and ht.deleted = 0   ";
        String order = " order by length(g.qc) ";
        StringBuilder where = new StringBuilder();
        if (qc != null) {
            where.append(" and ");
            where.append(" (g.qc like :qc or g.jc like :qc) ");
        }
        if (htId != null) {
            where.append(" and ");
            where.append(" ht.id = :htId  ");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where + order);
        if (qc != null) {
            query.setString("qc", "%" + qc.trim() + "%");
        }
        if (htId != null) {
            query.setLong("htId", htId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }


    public List<Map> findNbgsByHtId_qclist(Long htId, String qc, int pageNo, int pageSize) {
        String select = "select  distinct g.id  as \"id\",  g.qc  as  \"qc\" ";
        String from = "from t_biz_ht ht  " +
                " left join t_sys_gsjg g on g.id = ht.sbgs_id " +
                " where g.deleted = 0  and ht.deleted = 0    ";
        String order = " order by length(g.qc) ";
        StringBuilder where = new StringBuilder();
        if (qc != null) {
            where.append(" and ");
            where.append(" (g.qc like :qc or g.jc like :qc) ");
        }
        if (htId != null) {
            where.append(" and ");
            where.append(" ht.id = :htId  ");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where + order);
        if (qc != null) {
            query.setString("qc", "%" + qc.trim() + "%");
        }
        if (htId != null) {
            query.setLong("htId", htId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public Gsjg findGsjgIdByQc(String qc) {
        List<Gsjg> gsjgList = find("from Gsjg where qc = ? ", qc);
        Gsjg gsjg = CollectionUtils.isEmpty(gsjgList) ? null : gsjgList.get(0);
        return gsjg;
    }
}
